CREATE PROCEDURE DetermineMembershipLevel
    @customerUsername VARCHAR(20),
    @status int out
AS
BEGIN
    -- 声明变量
    DECLARE @totalAmount DECIMAL(11,2);
    DECLARE @membershipLevel INT;

    -- 计算该顾客的总月消费金额
    SELECT @totalAmount = SUM(o.whole_price)
    FROM history_order o
    WHERE o.customer_phone_number = (
        SELECT customer_phone_number
        FROM customer
        WHERE customer_username = @customerUsername
    )
      AND MONTH(o.paytime) = MONTH(GETDATE())
      AND YEAR(o.paytime) = YEAR(GETDATE());

    -- 根据总金额确定会员等级
    SET @membershipLevel = FLOOR(@totalAmount / 300);

    -- 更新顾客的会员等级
    UPDATE customer
    SET member = @membershipLevel
    WHERE customer_username = @customerUsername;
END;
go

